﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.OleDb;
using HotelManager.Data_Transfer_Layer;

namespace HotelManager.Data_Access_Layer
{
    class dichvuDAO
    {
        #region Variables
        private OleDbConnection conn;
        private string error;
        #endregion

        #region Properties
        public string Error
        {
            get { return error; }
            set { error = value; }
        }
        #endregion

        public dichvuDAO()
        {
            error = "";
            try
            {
                string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.\\hotel.accdb";
                conn = new OleDbConnection(strConn);
                conn.Open();
            }
            catch (Exception ex)
            {
                Error = "Không kết nối được đến cơ sở dữ liêu";
            }
        }

        public dichvuDTO[] get()
        {
            dichvuDTO[] objs;
            objs = new dichvuDTO[100];
            try
            {
                string sql = "SELECT * FROM dichvu";
                OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                OleDbDataReader reader = cmd.ExecuteReader();
                int i = 0, n = dt.Rows.Count;
                objs = new dichvuDTO[n];
                while (reader.Read())
                {
                    objs[i] = new dichvuDTO();
                    objs[i].Dv_id = reader.GetInt32(0);
                    objs[i].Ldv_id = reader.GetInt32(1);
                    objs[i].Dv_tendv = reader.GetString(2).ToString();
                    objs[i].Dv_soluong = reader.GetInt32(3);
                    objs[i].Dv_gia = reader.GetInt32(4);
                    objs[i].Dv_donvi = reader.GetString(5).ToString();
                    i++;
                }
            }
            catch (Exception ex)
            {

            }
            return objs;
        }

        public string[] get_arr()
        {
            string[] arr;
            dichvuDTO[] objs;
            arr = new string[100];
            objs = new dichvuDTO[100];
            try
            {
                string sql = "SELECT * FROM dichvu";
                OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                OleDbDataReader reader = cmd.ExecuteReader();
                int i = 0, n = dt.Rows.Count;
                objs = new dichvuDTO[n];
                arr = new string[n];
                while (reader.Read())
                {
                    objs[i] = new dichvuDTO();
                    objs[i].Dv_id = reader.GetInt32(0);
                    objs[i].Ldv_id = reader.GetInt32(1);
                    objs[i].Dv_tendv = reader.GetString(2).ToString();
                    objs[i].Dv_soluong = reader.GetInt32(3);
                    objs[i].Dv_gia = reader.GetInt32(4);
                    objs[i].Dv_donvi = reader.GetString(5).ToString();
                    arr[i] = objs[i].Dv_tendv;
                    i++;
                }
            }
            catch (Exception ex)
            {

            }
            return arr;
        }

        public dichvuDTO[] List_current(int ldv_id)
        {
            dichvuDTO[] objs;
            objs = new dichvuDTO[100];
            try
            {
                string sql = "SELECT * FROM dichvu WHERE ldv_id=" + ldv_id;
                OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                OleDbDataReader reader = cmd.ExecuteReader();
                int i = 0, n = dt.Rows.Count;
                objs = new dichvuDTO[n];
                while (reader.Read())
                {
                    objs[i] = new dichvuDTO();
                    objs[i].Dv_id = reader.GetInt32(0);
                    objs[i].Ldv_id = reader.GetInt32(1);
                    objs[i].Dv_tendv = reader.GetString(2).ToString();
                    objs[i].Dv_soluong = reader.GetInt32(3);
                    objs[i].Dv_gia = reader.GetInt32(4);
                    objs[i].Dv_donvi = reader.GetString(5).ToString();
                    i++;
                }
            }
            catch (Exception ex)
            {

            }
            return objs;
        }

        public dichvuDTO View_Detail(int dv_id)
        {
            dichvuDTO obj = new dichvuDTO();
            try
            {
                string sql = "SELECT * FROM dichvu WHERE dv_id=" + dv_id;
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                OleDbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    obj.Ldv_id = reader.GetInt32(1);
                    obj.Dv_tendv = reader.GetString(2).ToString();
                    obj.Dv_soluong = reader.GetInt32(3);
                    obj.Dv_gia = reader.GetInt32(4);
                    obj.Dv_donvi = reader.GetString(5).ToString();
                }
            }
            catch (Exception ex)
            {
                Error = ex.ToString();
            }
            return obj;
        }

        public dichvuDTO View_DetailbyName(string dv_tendv)
        {
            dichvuDTO obj = new dichvuDTO();
            try
            {
                string sql = "SELECT * FROM dichvu WHERE dv_tendv='" + dv_tendv + "'";
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                OleDbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    obj.Dv_id = reader.GetInt32(0);
                    obj.Ldv_id = reader.GetInt32(1);
                    obj.Dv_soluong = reader.GetInt32(3);
                    obj.Dv_gia = reader.GetInt32(4);
                    obj.Dv_donvi = reader.GetString(5).ToString();
                }
            }
            catch (Exception ex)
            {
                Error = ex.ToString();
            }
            return obj;
        }

        public void Delete(dichvuDTO obj)
        {
            try
            {
                string sql = "DELETE FROM dichvu WHERE dv_id=" + obj.Dv_id;
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Error = ex.ToString();
            }
        }

        public void Insert(dichvuDTO obj)
        {
            try
            {
                string sql = "INSERT INTO dichvu(ldv_id,dv_tendv,dv_soluong,dv_gia,dv_donvi) VALUES(" + obj.Ldv_id + ",'" + obj.Dv_tendv + "'," + obj.Dv_soluong + "," + obj.Dv_gia + ",'" + obj.Dv_donvi + "')";
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Error = ex.ToString();
            }
        }

        public void Update(dichvuDTO obj)
        {
            try
            {
                string sql1 = "UPDATE dichvu SET ldv_id=" + obj.Ldv_id + ",dv_tendv='" + obj.Dv_tendv + "',dv_soluong=" + obj.Dv_soluong + ",dv_gia=" + obj.Dv_gia + ",dv_donvi='" + obj.Dv_donvi + "' WHERE dv_id=" + obj.Dv_id;
                OleDbCommand cmd = new OleDbCommand(sql1, conn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Error = ex.ToString();
            }
        }
    }
}
